package com.weixin.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.mysql.jdbc.PreparedStatement;
import com.weixin.dao.IUser_tableDAO;
import com.weixin.util.C3p0Util;
import com.weixin.vo.Member;
import com.weixin.vo.MemberList;

public class User_tableDAOImpl implements IUser_tableDAO {

	/**
	 * 查询user_table表共有多少条数据
	 * @throws SQLException 
	 */
	@Override
	public int findTotal() throws SQLException {
		Connection connection = C3p0Util.getConnection();
		java.sql.PreparedStatement prepareStatement = connection.prepareStatement("select count(*) from user_table");
		ResultSet resultSet = prepareStatement.executeQuery();
		resultSet.next();
		int n = resultSet.getInt(1);
		connection.close();
		return n;
	}
	
	/**
	 * 根据会员编号查询会员信息
	 * @param user_id
	 * @return
	 * @throws SQLException 
	 */
	@Override
	public Member findById(int user_id) throws SQLException {
		 Member member = C3p0Util.qr.query("select * from user_table where user_id = ?", new BeanHandler<Member>(Member.class),user_id);
		 return member;
	}
	
	/**
	 * 会员充值
	 * money 充值金额，user_money 余额, user_id 会员编号
	 */
	@Override
	public boolean addMoney(double money ,double user_money, int user_id) throws SQLException {
		return C3p0Util.qr.update("update user_table set user_money=? where  user_id=?",user_money+money,user_id)>0;
	}
	
	/**
	 * 会员激活
	 * user_id 会员编号，acti_id 状态id
	 */
	@Override
	public boolean editActi_id(int user_id,int acti_id) throws SQLException {
		return C3p0Util.qr.update("update user_table set acti_id =? where user_id =?",acti_id,user_id)>0;
	}
	
	/**
	 * 会员信息
	 * 分页
	 * 搜索
	 * user_id 会员编号，pageNum 当前页数 ,pageSize 每页数据条数
	 */
	@Override
	public List<Member> selectInfoAndSearch(int user_id, int pageNum, int pageSize) throws SQLException {
		List<Member> list = new ArrayList<>();
		StringBuffer sql = new StringBuffer("select * "+
										"from user_table left join acti_table on user_table.acti_id=acti_table.acti_id ");
		if(user_id==0) {
			sql.append(" limit ?,?");
			list = C3p0Util.qr.query(sql.toString(), new BeanListHandler<Member>(Member.class),(pageNum-1)*pageSize,pageSize);
		}else {
			sql.append(" where user_table.user_id=? limit ?,?");
			list = C3p0Util.qr.query(sql.toString(),new BeanListHandler<Member>(Member.class),user_id,(pageNum-1)*pageSize,pageSize);
		}
		return list;
	}

	
}
